﻿alter table Goal add MeasurementTypeId int null
alter table Goal add constraint fk_GoalMeasurementType foreign key (MeasurementTypeId) references MeasurementType(MeasurementTypeId)
go
begin transaction

declare @weightId int
declare @bodyFatId int
declare @bmiId int
declare @waistId int
declare @chestId int
declare @armsId int
declare @thighsId int
declare @heightId int
select @weightId = MeasurementTypeId from MeasurementType where Description='Weight'
select @bodyFatId = MeasurementTypeId from MeasurementType where Description='Body Fat %'
select @bmiId = MeasurementTypeId from MeasurementType where Description='BMI'
select @waistId = MeasurementTypeId from MeasurementType where Description='Waist'
select @chestId = MeasurementTypeId from MeasurementType where Description='Chest'
select @armsId = MeasurementTypeId from MeasurementType where Description='Arms'
select @thighsId = MeasurementTypeId from MeasurementType where Description='Thighs'
select @heightId = MeasurementTypeId from MeasurementType where Description='Height'

update Goal set MeasurementTypeId = @weightId where KeyIndicator = 'W'

update Goal set MeasurementTypeId = @bodyFatId where KeyIndicator = 'BF'

update Goal set MeasurementTypeId = @bmiId where KeyIndicator = 'BM'

update Goal set MeasurementTypeId = @waistId where KeyIndicator = 'WA'

update Goal set MeasurementTypeId = @chestId where KeyIndicator = 'CH'

update Goal set MeasurementTypeId = @armsId where KeyIndicator = 'AR'

update Goal set MeasurementTypeId = @thighsId where KeyIndicator = 'TH'

update Goal set MeasurementTypeId = @heightId where KeyIndicator = 'H'

commit transaction
go
alter table Goal alter column MeasurementTypeId int not null
alter table Goal drop column KeyIndicator
go